package com.code;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;

import com.Global.Global;
import com.database.DBSystem;
import com.example.R;
import com.object.obCuocGoi;

import android.app.Activity;
import android.content.Intent;
import android.database.Cursor;
import android.graphics.Color;
import android.os.Bundle;
import android.view.Gravity;
import android.view.View;
import android.view.View.OnClickListener;
import android.view.View.OnLongClickListener;
import android.widget.LinearLayout;
import android.widget.TableLayout;
import android.widget.TableRow;
import android.widget.TextView;

public class DSCuocGoi extends Activity {

	private DBSystem dbHelper;
	private LinearLayout llMain;
	private final int REQUESTCODEDETAIL = 100;
	private ArrayList<Integer> date_actual;
	private static String day;
	private static String month;
	private static String year;
	private static String date;
	// private static StringBuffer sql;
	private static int choose_mau;
	private static String strKho = "";

	@Override
	protected void onCreate(Bundle savedInstanceState) {
		// TODO Auto-generated method stub
		super.onCreate(savedInstanceState);
		setContentView(R.layout.dscuocgoi);

		// declare component
		llMain = (LinearLayout) findViewById(R.id.llMain);

		// get date 19/03/2014
		date_actual = super.getIntent().getExtras().getIntegerArrayList("date");
		day = date_actual.get(0).toString();
		month = date_actual.get(1).toString();
		year = date_actual.get(2).toString();
		date = String.valueOf(date_actual.get(2))
				+ DetailCuocGoi.setMonth(date_actual.get(1))
				+ Global.formatDay(date_actual.get(0));
		//
		choose_mau = super.getIntent().getExtras().getInt("choose_mau");
		strKho = super.getIntent().getExtras().getString("strKho");
		selectAllCuocGoiFromSQLSERVER(day, month, year, choose_mau);
	}

	public Boolean selectAllCuocGoiFromSQLSERVER(String day, String month,
			String year, int type) {
		Connection conn = null;
		try {
			String driver = "net.sourceforge.jtds.jdbc.Driver";
			Class.forName(driver).newInstance();

			dbHelper = new DBSystem(this);
			dbHelper.open();
			Cursor cur = dbHelper.SelectSystemAll();
			String strServerName = "";
			String strServerPort = "";
			String strLogin = "";
			String strPassword = "";
			String strDatabaseName = "";
			if (cur != null) {
				while (cur.moveToNext()) {
					strServerName = cur.getString(1).toString();
					strServerPort = cur.getString(2).toString();
					strLogin = cur.getString(3).toString();
					strPassword = cur.getString(4).toString();
					strDatabaseName = cur.getString(5).toString();
				}
				cur.close();
			}
			dbHelper.close();
			String url = "jdbc:jtds:sqlserver://" + strServerName + ":"
					+ strServerPort + ";DatabaseName=" + strDatabaseName
					+ ";integratedSecurity=true;user=" + strLogin
					+ ";password=" + strPassword + "";
			conn = DriverManager.getConnection(url);
			String sql = "";
			if (choose_mau == TK_ChonMau.CHOOSE_CUOCGOI_CHUABAN)
				sql = getSQLByCuocGoiKhongBan().toString();
			else
				sql = getSQL(choose_mau).toString();
			PreparedStatement stmt = conn.prepareStatement(sql);
			stmt.execute();
			ResultSet rs = stmt.getResultSet();
			if (choose_mau == TK_ChonMau.CHOOSE_CUOCGOI_CHUABAN)
				loadCuocGoiKhongBan(rs);
			else
				loadCuocGoi(rs);
			stmt.close();
			conn.close();
			return true;
		} catch (Exception e) {
			return false;
		}
	}

	private void loadCuocGoi(final ResultSet rs) throws SQLException {
		Float totalTienBan = 0.0f, tienThu = 0.f;
		Integer sl = 0, slTra = 0;
		final TableLayout table = new TableLayout(this);
		llMain.addView(table);
		TableRow trHead = new TableRow(this);
		trHead.setWeightSum(4.0f);

		TextView tvPhone = new TextView(this);
		tvPhone.setText("Điện thoại");
		TK_TongMatHang.setLayoutForTextView(tvPhone, true, Gravity.CENTER, 10,
				1.0f);

		TextView tvTenKH = new TextView(this);
		tvTenKH.setText("Khách hàng");
		TK_TongMatHang.setLayoutForTextView(tvTenKH, true, Gravity.CENTER, 10,
				1.0f);

		TextView tvSanPham = new TextView(this);
		tvSanPham.setText("Mã hàng");
		TK_TongMatHang.setLayoutForTextView(tvSanPham, true, Gravity.CENTER,
				10, 1.5f);

		TextView tvTienBan = new TextView(this);
		tvTienBan.setText("Tiền bán");
		TK_TongMatHang.setLayoutForTextView(tvTienBan, true, Gravity.RIGHT, 10,
				0.5f, true);

		trHead.addView(tvPhone);
		trHead.addView(tvTenKH);
		trHead.addView(tvSanPham);
		trHead.addView(tvTienBan);
		table.addView(trHead);

		while (rs.next()) {

			final obCuocGoi CuocGoi = new obCuocGoi();
			if (rs.getString("MaKH") != null)
				CuocGoi.setID(rs.getString("MaKH").toString());
			if (rs.getString("MaSP") != null)
				CuocGoi.setMaSP(rs.getString("MaSP").toString());
			if (rs.getString("Phone") != null)
				CuocGoi.setPhone(rs.getString("Phone").toString());
			if (rs.getString("Ngay") != null)
				CuocGoi.setNgay(rs.getString("Ngay").toString());
			if (rs.getString("DiaChi") != null)
				CuocGoi.setDiaChi(rs.getString("DiaChi").toString());
			if (rs.getString("TenKhachHang") != null)
				CuocGoi.setTenKhachHang(rs.getString("TenKhachHang").toString());

			//
			if (rs.getString("TienBan") != null)
				CuocGoi.setTienBan(rs.getString("TienBan"));
			else
				CuocGoi.setTienBan("0.0f");
			if (rs.getString("TienThu") != null)
				tienThu = tienThu
						+ Float.valueOf(rs.getString("TienThu").toString()
								.equals("") ? "0" : rs.getString("TienThu")
								.toString());
			if (rs.getString("SL") != null) {
				// sl = sl +
				// Double.valueOf(rs.getString("SL").toString().equals("") ?
				// "0.0" : rs.getString("SL").toString() );
				if (rs.getString("SL").toString().equals("")
						|| rs.getString("SL").toString().equals("NULL")) {
					sl = sl + 0;
				} else
					sl = sl + rs.getInt("SL");
			}
			if (rs.getString("SLTra") != null) {
				if (rs.getString("SLTra").toString().equals("NULL")
						|| rs.getString("SLTra").toString().equals("")) {
					slTra = slTra + 0;
				} else
					slTra = slTra + rs.getInt("SLTra");

			}
			// end
			TableRow tr = new TableRow(this);

			tr.setOnClickListener(new OnClickListener() {
				public void onClick(View v) {
					for (int i = 0; i < table.getChildCount(); i++) {
						table.getChildAt(i).setBackgroundColor(
								Color.TRANSPARENT);
					}
					v.setBackgroundColor(Color.YELLOW);
				}
			});

			tr.setOnLongClickListener(new OnLongClickListener() {
				public boolean onLongClick(View v) {
					// get date
					Timestamp ngay = Timestamp.valueOf(CuocGoi.getNgay());
					final String nam = String.valueOf(ngay.getYear() + 1900);
					ArrayList<String> detail = new ArrayList<String>();
					ArrayList<String> detail1 = getDetailCalByCustomer(
							CuocGoi.getID(), CuocGoi.getNgay(), nam);
					// ngay truoc and chu ky
					String ngayTruoc = "", ngayKeTiep = "";
					Integer chuky = 0;
					if (getMaxDateShipmentCustomer(CuocGoi.getNgay(),
							CuocGoi.getID()) != null) {
						if (getMaxPreviousDateOfMaxDateShipmentCustomer(
								CuocGoi.getNgay(), CuocGoi.getID()) != null) {
							ngayTruoc = getMaxPreviousDateOfMaxDateShipmentCustomer(
									CuocGoi.getNgay(), CuocGoi.getID())
									.toString();
							chuky = getChuKyLayHang(
									getMaxPreviousDateOfMaxDateShipmentCustomer(
											CuocGoi.getNgay(), CuocGoi.getID()),
									getMaxDateShipmentCustomer(
											CuocGoi.getNgay(), CuocGoi.getID()));
						}
						if (chuky > 0)
							ngayKeTiep = ngayKeTiep(
									chuky,
									getMaxDateShipmentCustomer(
											CuocGoi.getNgay(), CuocGoi.getID()))
									.toString();

					}

					String dateInput = year
							+ DetailCuocGoi.setMonth(date_actual.get(1)) + day;
					// du nocuoiky
					Float duNo = 0.0f;
					duNo = Float.valueOf(duNoCuoiKy(CuocGoi.getID(), dateInput));
					detail.add(0, CuocGoi.getID());
					detail.add(1, CuocGoi.getMaSP());
					detail.add(2, CuocGoi.getPhone());
					detail.add(3, CuocGoi.getNgay());
					detail.add(4, CuocGoi.getDiaChi());
					detail.add(5, CuocGoi.getTenKhachHang());
					detail.add(6, Global.formatCurrency(CuocGoi.getTienBan()));
					if (detail1 != null && detail1.size() != 0) {
						detail.add(7, detail1.get(0).toString());
						// ngay truoc
						detail.add(8, ngayTruoc);
						// chu ky lay hang
						detail.add(9, chuky.toString());
						// ngay ke tiep lay hang
						detail.add(10, ngayKeTiep.toString());
						// du no cuoi ky
						detail.add(11, Global.formatDecimalDetail(duNo));
						// tong thanh tien
						detail.add(
								12,
								Global.formatDecimalDetail(
										Float.valueOf(detail1.get(1)))
										.toString());
						// ngay ban gan nhat
						if (getMaxDateShipmentCustomer(CuocGoi.getNgay(),
								CuocGoi.getID()) != null) {
							detail.add(
									13,
									getMaxDateShipmentCustomer(
											CuocGoi.getNgay(), CuocGoi.getID())
											.toString());
						} else {
							detail.add(13, "");
						}
						if (!CuocGoi.getID().equals("")) {
							ArrayList<Integer> dateDetail = new ArrayList<Integer>();
							dateDetail.add(0, date_actual.get(0));
							dateDetail.add(1, date_actual.get(1));
							dateDetail.add(2, date_actual.get(2));
							Intent i = new Intent(DSCuocGoi.this,
									DetailCuocGoi.class);
							Bundle b = new Bundle();
							b.putStringArrayList("detail", detail);
							b.putIntegerArrayList("date_detail", dateDetail);
							i.putExtras(b);
							startActivityForResult(i, REQUESTCODEDETAIL);
						}
					} else {
						detail.add(7, "");
						detail.add(8, "");
						detail.add(9, "");
						detail.add(10, "");
						detail.add(11, Global.formatDecimalDetail(duNo));
						detail.add(12, "0");
						detail.add(
								13,
								getMaxDateShipmentCustomer(CuocGoi.getNgay(),
										CuocGoi.getID()).toString());
						if (!CuocGoi.getID().equals("")) {
							ArrayList<Integer> dateDetail = new ArrayList<Integer>();
							dateDetail.add(0, date_actual.get(0));
							dateDetail.add(1, date_actual.get(1));
							dateDetail.add(2, date_actual.get(2));
							Intent i = new Intent(DSCuocGoi.this,
									DetailCuocGoi.class);
							Bundle b = new Bundle();
							b.putStringArrayList("detail", detail);
							b.putIntegerArrayList("date_detail", dateDetail);
							i.putExtras(b);
							startActivityForResult(i, REQUESTCODEDETAIL);
						}
					}
					return false;
				}
			});
			tr.setWeightSum(4.0f);

			TextView tvPhones = new TextView(this);
			tvPhones.setText(CuocGoi.getPhone());
			TK_TongMatHang.setLayoutForTextView(tvPhones, false,
					Gravity.CENTER, 10, 1.0f);

			TextView tvTenKHs = new TextView(this);
			tvTenKHs.setText(CuocGoi.getTenKhachHang());
			TK_TongMatHang.setLayoutForTextView(tvTenKHs, false, Gravity.LEFT,
					10, 1.0f);

			TextView tvSanPhams = new TextView(this);
			tvSanPhams.setText(CuocGoi.getMaSP());
			TK_TongMatHang.setLayoutForTextView(tvSanPhams, false,
					Gravity.LEFT, 10, 1.5f);

			TextView tvTienBans = new TextView(this);
			tvTienBans.setText(Global.formatDecimal(Float.valueOf(CuocGoi
					.getTienBan())));
			TK_TongMatHang.setLayoutForTextView(tvTienBans, false,
					Gravity.RIGHT, 5, 0.5f, true);
			// tvTienBans.setGravity(Gravity.RIGHT);

			tr.addView(tvPhones);
			tr.addView(tvTenKHs);
			tr.addView(tvSanPhams);
			tr.addView(tvTienBans);
			table.addView(tr);

			totalTienBan = totalTienBan
					+ Float.valueOf(CuocGoi.getTienBan().equals("") ? "0"
							: CuocGoi.getTienBan());
		}

		// head
		TableRow trHeadEnd = new TableRow(this);
		trHeadEnd.setWeightSum(4.0f);

		TextView tvPhoneHeadEnd = new TextView(this);
		tvPhoneHeadEnd.setText("Tồng tiền");
		TK_TongMatHang.setLayoutForTextView(tvPhoneHeadEnd, true,
				Gravity.CENTER, 10, 1.0f);

		TextView tvTenKHHeadEnd = new TextView(this);
		tvTenKHHeadEnd.setText("Tiền thu");
		TK_TongMatHang.setLayoutForTextView(tvTenKHHeadEnd, true,
				Gravity.CENTER, 10, 1.0f);

		TextView tvSanPhamHeadEnd = new TextView(this);
		tvSanPhamHeadEnd.setText("Nước");
		TK_TongMatHang.setLayoutForTextView(tvSanPhamHeadEnd, true,
				Gravity.CENTER, 10, 1.5f);

		TextView tvTienBanHeadEnd = new TextView(this);
		tvTienBanHeadEnd.setText("Vỏ về");
		TK_TongMatHang.setLayoutForTextView(tvTienBanHeadEnd, true,
				Gravity.CENTER, 10, 0.5f, true);

		trHeadEnd.addView(tvPhoneHeadEnd);
		trHeadEnd.addView(tvTenKHHeadEnd);
		trHeadEnd.addView(tvSanPhamHeadEnd);
		trHeadEnd.addView(tvTienBanHeadEnd);
		table.addView(trHeadEnd);

		// end head
		TableRow trEnd = new TableRow(this);
		trEnd.setWeightSum(4.0f);

		TextView tvPhoneEnd = new TextView(this);
		tvPhoneEnd.setText(Global.formatDecimal(totalTienBan));
		TK_TongMatHang.setLayoutForTextView(tvPhoneEnd, true, Gravity.CENTER,
				10, 1.0f);
		tvPhoneEnd.setBackgroundResource(R.layout.border_topleftbottom);

		TextView tvTenKHEnd = new TextView(this);
		tvTenKHEnd.setText(Global.formatDecimal(tienThu));
		TK_TongMatHang.setLayoutForTextView(tvTenKHEnd, true, Gravity.CENTER,
				10, 1.0f);
		tvTenKHEnd.setBackgroundResource(R.layout.border_topleftbottom);

		TextView tvSanPhamEnd = new TextView(this);
		tvSanPhamEnd.setText(sl.toString());
		TK_TongMatHang.setLayoutForTextView(tvSanPhamEnd, true, Gravity.CENTER,
				10, 1.5f);
		tvSanPhamEnd.setBackgroundResource(R.layout.border_topleftbottom);

		TextView tvTienBanEnd = new TextView(this);
		tvTienBanEnd.setText(slTra.toString());
		TK_TongMatHang.setLayoutForTextView(tvTienBanEnd, true, Gravity.CENTER,
				5, 0.5f, true);
		tvTienBanEnd.setBackgroundResource(R.layout.border);

		trEnd.addView(tvPhoneEnd);
		trEnd.addView(tvTenKHEnd);
		trEnd.addView(tvSanPhamEnd);
		trEnd.addView(tvTienBanEnd);
		table.addView(trEnd);

	}

	public static ArrayList<String> getDetailCalByCustomer(String id,
			String date, String nam) {
		ArrayList<String> list = new ArrayList<String>();
		StringBuffer sql = new StringBuffer(
				" SELECT  CASE WHEN (ISNULL(SUM(vattuxuat.SL),'')='') then 0 else SUM(vattuxuat.SL)END SL ,");
		sql.append(" CASE WHEN (ISNULL(SUM(vattuxuat.sl*vattuxuat.dongia*(1+isnull(vattuxuat.thue,0))),'') = '') THEN 0 ");
		sql.append(" ELSE SUM(vattuxuat.sl*vattuxuat.dongia*(1+isnull(vattuxuat.thue,0))) END ");
		sql.append(" FROM Suppliers sp , Phieuxuat px INNER JOIN vattuxuat ");
		sql.append(" ON px.SoCt = vattuxuat.SoCT INNER JOIN Products ");
		sql.append(" ON vattuxuat.[Product ID] = Products.[Product ID] ");
		sql.append(" WHERE px.[Supplier ID] = '" + id + "' ");
		sql.append(" AND cast(px.Ngay as date) <= cast('" + date + "' as date)");
		sql.append(" AND isnull(Products.TheoDoiVo,0)<>0 ");
		sql.append(" AND DATEPART(YYYY,px.Ngay) = " + nam);
		sql.append(" AND px.[Supplier ID] = sp.[Supplier ID]");

		ResultSet rs = Global.excuteQuerySQLServer(sql.toString());
		try {
			while (rs.next()) {
				list.add(0, String.valueOf(rs.getInt(1)));// so luong
				list.add(1, rs.getString(2));// thanh tien
				rs.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	public static StringBuffer getSQL(int type) {
		StringBuffer sql = new StringBuffer(
				" SELECT [Supplier ID]as MaKH, CASE WHEN [Company Name] IS NULL THEN MaSP ELSE [Company Name] END AS MaSP, Phone, "
						+ " Ngay, DiaChi, TenKhachHang, TienBan , TienThu, SL, SLTra ");
		sql.append(" FROM CuocGoi ");
		sql.append(" WHERE DATEPART(DD, Ngay) = '" + day + "'");
		sql.append(" AND DATEPART(MM, Ngay) = '" + month + "'");
		sql.append(" AND DATEPART(YY, Ngay) = '" + year + "'");
		if (!strKho.equals(""))
			sql.append(" AND ( MSK COLLATE DATABASE_DEFAULT in (SELECT value FROM dbo.fn_Split('"
					+ strKho + "', ',')) OR ISNULL(MSK ,'') = '')");
		if (type == TK_ChonMau.CHOOSE_CHUAGIAO) {
			sql.append(" AND ISNULL(NguoiGiao,'') ='' ");
			sql.append(" AND ISNULL(SoPhieu,'') !='' ");
		}
		if (type == TK_ChonMau.CHOOSE_CHUATHANHTOAN) {
			sql.append(" AND ISNULL(SoCTThu,'') ='' "
					+ " AND ISNULL(SoPhieu,'') !='' "
					+ " AND ISNULL(TienBan,'') <> '' "
					+ " AND ISNULL(TienBan,0) <> 0 ");
		}
		if (type == TK_ChonMau.CHOOSE_CHUAGIAO_CHUATHANTOAN) {
			sql.append(" AND ISNULL(SoPhieu,'') !='' "
					+ " AND (ISNULL(NguoiGiao,'') = '' OR (ISNULL(SoCTThu,'') = '' "
					+ " AND ISNULL(TienBan,'') <> ''AND ISNULL(TienBan,0) <> 0 ))");
		}
		if (type == TK_ChonMau.CHOOSE_CUOCGOI_DABAN) {
			sql.append(" AND ISNULL(SoPhieu,'') !='' ");
		}
		// if (type == TK_ChonMau.CHOOSE_CUOCGOI_CHUABAN) {
		// sql.append(" AND ISNULL(SoPhieu,'') ='' ");
		// }
		sql.append(" ORDER BY Ngay DESC ");
		return sql;
	}

	public static String duNoCuoiKy(String supplierID, String date) {
		String duNo = "0";
		String sql = " SELECT dbo.Dunocuoiky( '" + date + "','" + supplierID
				+ "')";
		ResultSet rs = Global.excuteQuerySQLServer(sql);
		try {
			if (rs.next())
				duNo = rs.getString(1);

		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return duNo;
	}

	// get date max Shipment Customer
	public static Timestamp getMaxDateShipmentCustomer(String date,
			String supplierID) {
		Timestamp t = null;
		String sql = " SELECT Max(px1.Ngay)" + " FROM Phieuxuat px1 "
				+ " WHERE px1.[Supplier ID] = '" + supplierID + "'"
				+ " AND px1.Ngay <= cast('" + date + "' as date)";
		ResultSet rs = Global.excuteQuerySQLServer(sql);
		try {
			if (rs.next())
				t = rs.getTimestamp(1);
			else
				t = null;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return t;
	}

	// get date Max after date max Shipment Customer
	public static Timestamp getMaxPreviousDateOfMaxDateShipmentCustomer(
			String date, String supplierID) {
		Timestamp t = null;
		String sql = " SELECT Max(px1.Ngay)" + " FROM Phieuxuat px1 "
				+ " WHERE px1.[Supplier ID] = '" + supplierID + "'"
				+ " AND px1.Ngay <= DATEADD(DD,-1,cast( "
				+ " (SELECT Max(px1.Ngay) " + " FROM Phieuxuat px1 "
				+ " WHERE px1.[Supplier ID] = '" + supplierID + "' "
				+ " AND px1.Ngay <= cast('" + date + "' as date)) "
				+ " as date))";
		ResultSet rs = Global.excuteQuerySQLServer(sql);
		try {
			if (rs.next())
				t = rs.getTimestamp(1);
			else
				t = null;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return t;
	}

	// Calculator chu ky lay hang
	public static int getChuKyLayHang(Timestamp datePreviousMAx,
			Timestamp dateMax) {
		int chuKy = 0;
		String sql = " SELECT DATEDIFF(DD,'" + datePreviousMAx + "','"
				+ dateMax + "')";
		ResultSet rs = Global.excuteQuerySQLServer(sql);
		try {
			if (rs.next())
				chuKy = rs.getInt(1);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return chuKy;
	}

	// date next
	public static Timestamp ngayKeTiep(Integer chuKy, Timestamp date) {
		Timestamp t = null;
		String sql = " SELECT DATEADD(DD," + chuKy + ",'" + date + "')";
		ResultSet rs = Global.excuteQuerySQLServer(sql);
		try {
			if (rs.next())
				t = rs.getTimestamp(1);
			else
				t = null;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return t;
	}

	/**
	 * get sql ds cuoc goi khong ban
	 * 
	 * @return date 22/04/2014
	 */
	public static StringBuffer getSQLByCuocGoiKhongBan() {
		StringBuffer sql = new StringBuffer("spMobileDanhSachCuocGoiKhongBan '"
				+ date + "', '" + date + "','" + strKho + "'");
		return sql;
	}

	private void loadCuocGoiKhongBan(final ResultSet rs) throws SQLException {
		String phone = "";
		final TableLayout table = new TableLayout(this);
		llMain.addView(table);
		TableRow trHead = new TableRow(this);
		trHead.setWeightSum(4.0f);

		TextView tvPhone = new TextView(this);
		tvPhone.setText("Điện thoại");
		TK_TongMatHang.setLayoutForTextView(tvPhone, true, Gravity.CENTER, 10,
				1.0f);

		TextView tvTenKH = new TextView(this);
		tvTenKH.setText("Khách hàng");
		TK_TongMatHang.setLayoutForTextView(tvTenKH, true, Gravity.CENTER, 10,
				1.0f);

		TextView tvGhiChu = new TextView(this);
		tvGhiChu.setText("Ghi chú");
		TK_TongMatHang.setLayoutForTextView(tvGhiChu, true, Gravity.CENTER, 10,
				2.0f, true);
		tvGhiChu.setBackgroundResource(R.layout.border);

		trHead.addView(tvPhone);
		trHead.addView(tvTenKH);
		trHead.addView(tvGhiChu);
		table.addView(trHead);

		while (rs.next()) {

			final obCuocGoi CuocGoi = new obCuocGoi();
			if (rs.getString("Phone") != null) {
//				if (!rs.getString("Phone").toString().trim().equals(phone)) {
					CuocGoi.setPhone(rs.getString("Phone").toString().trim());
//					phone = rs.getString("Phone").toString().trim();
//				}else
//					continue;
			}
			if (rs.getString("MaKH") != null)
				CuocGoi.setID(rs.getString("MaKH").toString());
//			if (rs.getString("MaSP") != null)
//				CuocGoi.setMaSP(rs.getString("MaSP").toString());
			if (rs.getString("Ngay") != null)
				CuocGoi.setNgay(rs.getString("Ngay").toString());
			if (rs.getString("DiaChi") != null)
				CuocGoi.setDiaChi(rs.getString("DiaChi").toString());
			if (rs.getString("TenKhachHang") != null)
				CuocGoi.setTenKhachHang(rs.getString("TenKhachHang").toString());
			if (rs.getString("GhiChu") != null)
				CuocGoi.setGhiChu(rs.getString("GhiChu"));
			else
				CuocGoi.setGhiChu("");
			// end
			TableRow tr = new TableRow(this);

			tr.setOnClickListener(new OnClickListener() {
				public void onClick(View v) {
					for (int i = 0; i < table.getChildCount(); i++) {
						table.getChildAt(i).setBackgroundColor(
								Color.TRANSPARENT);
					}
					v.setBackgroundColor(Color.YELLOW);
				}
			});

			tr.setOnLongClickListener(new OnLongClickListener() {
				public boolean onLongClick(View v) {
					// get date
					Timestamp ngay = Timestamp.valueOf(CuocGoi.getNgay());
					final String nam = String.valueOf(ngay.getYear() + 1900);
					ArrayList<String> detail = new ArrayList<String>();
					ArrayList<String> detail1 = getDetailCalByCustomer(
							CuocGoi.getID(), CuocGoi.getNgay(), nam);
					// ngay truoc and chu ky
					String ngayTruoc = "", ngayKeTiep = "";
					Integer chuky = 0;
					if (getMaxDateShipmentCustomer(CuocGoi.getNgay(),
							CuocGoi.getID()) != null) {
						if (getMaxPreviousDateOfMaxDateShipmentCustomer(
								CuocGoi.getNgay(), CuocGoi.getID()) != null) {
							ngayTruoc = getMaxPreviousDateOfMaxDateShipmentCustomer(
									CuocGoi.getNgay(), CuocGoi.getID())
									.toString();
							chuky = getChuKyLayHang(
									getMaxPreviousDateOfMaxDateShipmentCustomer(
											CuocGoi.getNgay(), CuocGoi.getID()),
									getMaxDateShipmentCustomer(
											CuocGoi.getNgay(), CuocGoi.getID()));
						}
						if (chuky > 0)
							ngayKeTiep = ngayKeTiep(
									chuky,
									getMaxDateShipmentCustomer(
											CuocGoi.getNgay(), CuocGoi.getID()))
									.toString();

					}

					String dateInput = year
							+ DetailCuocGoi.setMonth(date_actual.get(1)) + day;
					// du nocuoiky
					Float duNo = 0.0f;
					duNo = Float.valueOf(duNoCuoiKy(CuocGoi.getID(), dateInput));
					detail.add(0, CuocGoi.getID());
					detail.add(1, CuocGoi.getMaSP());
					detail.add(2, CuocGoi.getPhone());
					detail.add(3, CuocGoi.getNgay());
					detail.add(4, CuocGoi.getDiaChi());
					detail.add(5, CuocGoi.getTenKhachHang());
					detail.add(6, "");
					if (detail1 != null && detail1.size() != 0) {
						detail.add(7, detail1.get(0).toString());
						// ngay truoc
						detail.add(8, ngayTruoc);
						// chu ky lay hang
						detail.add(9, chuky.toString());
						// ngay ke tiep lay hang
						detail.add(10, ngayKeTiep.toString());
						// du no cuoi ky
						detail.add(11, Global.formatDecimalDetail(duNo));
						// tong thanh tien
						detail.add(
								12,
								Global.formatDecimalDetail(
										Float.valueOf(detail1.get(1)))
										.toString());
						// ngay ban gan nhat
						if (getMaxDateShipmentCustomer(CuocGoi.getNgay(),
								CuocGoi.getID()) != null) {
							detail.add(
									13,
									getMaxDateShipmentCustomer(
											CuocGoi.getNgay(), CuocGoi.getID())
											.toString());
						} else {
							detail.add(13, "");
						}
						if (!CuocGoi.getID().equals("")) {
							ArrayList<Integer> dateDetail = new ArrayList<Integer>();
							dateDetail.add(0, date_actual.get(0));
							dateDetail.add(1, date_actual.get(1));
							dateDetail.add(2, date_actual.get(2));
							Intent i = new Intent(DSCuocGoi.this,
									DetailCuocGoi.class);
							Bundle b = new Bundle();
							b.putStringArrayList("detail", detail);
							b.putIntegerArrayList("date_detail", dateDetail);
							i.putExtras(b);
							startActivityForResult(i, REQUESTCODEDETAIL);
						}
					} else {
						detail.add(7, "");
						detail.add(8, "");
						detail.add(9, "");
						detail.add(10, "");
						detail.add(11, Global.formatDecimalDetail(duNo));
						detail.add(12, "0");
						detail.add(
								13,
								getMaxDateShipmentCustomer(CuocGoi.getNgay(),
										CuocGoi.getID()).toString());
						if (!CuocGoi.getID().equals("")) {
							ArrayList<Integer> dateDetail = new ArrayList<Integer>();
							dateDetail.add(0, date_actual.get(0));
							dateDetail.add(1, date_actual.get(1));
							dateDetail.add(2, date_actual.get(2));
							Intent i = new Intent(DSCuocGoi.this,
									DetailCuocGoi.class);
							Bundle b = new Bundle();
							b.putStringArrayList("detail", detail);
							b.putIntegerArrayList("date_detail", dateDetail);
							i.putExtras(b);
							startActivityForResult(i, REQUESTCODEDETAIL);
						}
					}
					return false;
				}
			});
			tr.setWeightSum(4.0f);

			TextView tvPhones = new TextView(this);
			tvPhones.setText(CuocGoi.getPhone());
			TK_TongMatHang.setLayoutForTextView(tvPhones, false,
					Gravity.CENTER, 10, 1.0f);
			if (rs.isLast())
				tvPhones.setBackgroundResource(R.layout.border_topleftbottom);

			TextView tvTenKHs = new TextView(this);
			tvTenKHs.setText(CuocGoi.getTenKhachHang());
			TK_TongMatHang.setLayoutForTextView(tvTenKHs, false, Gravity.LEFT,
					10, 1.0f);
			if (rs.isLast())
				tvTenKHs.setBackgroundResource(R.layout.border_topleftbottom);

			TextView tvGhiChus = new TextView(this);
			tvGhiChus.setText(CuocGoi.getGhiChu());
			TK_TongMatHang.setLayoutForTextView(tvGhiChus, false, Gravity.LEFT,
					10, 2.0f, true);
			if (rs.isLast())
				tvGhiChus.setBackgroundResource(R.layout.border);
			tr.addView(tvPhones);
			tr.addView(tvTenKHs);
			tr.addView(tvGhiChus);
			table.addView(tr);
		}

	}
}
